package com.zdkj.subway.check.controller.stationTiecketManager.xianWngCJXXQR.shouRuJS;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.zdkj.common.core.util.SpringContextHolder;
import com.zdkj.subway.check.entity.SwStConductorSettle;
import com.zdkj.subway.check.entity.SwStInputPassengerWork;
import com.zdkj.subway.check.entity.SwStMarketingTicket;
import com.zdkj.subway.check.entity.SwStMarketingTicketGroupSell;
import com.zdkj.subway.check.entity.SwStMarketingTicketOffineSell;
import com.zdkj.subway.check.mapper.SwStInputPassengerWorkMapper;
import com.zdkj.subway.check.mapper.SwStMarketingTicketMapper;
import com.zdkj.subway.check.mapper.SwStMarketingTicketGroupSellMapper;
import com.zdkj.subway.check.mapper.SwStMarketingTicketOffineSellMapper;
import com.zdkj.subway.check.util.DateFormat;
import com.zdkj.subway.check.util.JdbcUtil;
import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.*;

/**
 *  BOM车站提交工具，
 */
public class BomUtil {

    /**
	   收入结算-待提交的查询
	*/
	 public static  List<Map<String, Object>> getXw_bom_shouJieSuan_waitSubmit(Map<String,String> param) throws SQLException {
        String sql="select distinct "+
                "scs.ID id,\n" +
                "scs.LINE_CODE lineCode,\n" +
                "scs.STATION_CODE stationCode,\n" +
                "scs.WORKING_START_TIME workingStartTime,\n" +
                "scs.WORKING_END_TIME workingEndTime,\n" +
                "scs.CONDUCTOR_ID conductorId,\n" +
                "scs.CONDUCTOR_NAME conductorName,\n" +
                "scs.CONDUCTOR_JOB_NO conductorJobNo,\n" +
                "scs.CONDUCTOR_SHIFTS conductorShifts,\n" +
                "scs.MORNING_AND_EVENING_SHIFTS morningAndEveningShifts,\n" +
                "scs.DEVICE_OPERATE_NUM deviceOperateNum,\n" +
                "scs.ADANCE_AMOUNT adanceAmount,\n" +
                "scs.REAL_AMOUNT realAmount,\n" +
                "scs.BOM_CODE bomCode,\n" +
                "scs.SETTLE_TIME settleTime,\n" +
                "scs.APPROVAL_STATUS approvalStatus,\n" +
                "scs.CONFIRM_STATUS confirmStatus,\n" +
                "scs.REALTION_MARKETING_ID realtionMarketingId,\n" +
                "scs.REMARK remark,\n" +
			    "scs.GUARD_TIME guardTime,\n" +
                "scs.DEPT_ID deptId,\n" +
                "scs.CREATOR_ID creatorId,\n" +
                "scs.CREATOR_NAME creatorName,\n" +
                "scs.CREATE_TIME createTime,\n" +
                "scs.UPDATE_TIME updateTime,\n" +
                "scs.DEL_FLAG delFlag,"+
			    "scs.PASSENGER_ATTENDANT_JOB_NO passengerAttendantJobNo,\n"+
			    "scs.PASSENGER_ATTENDANT_NAME passengerAttendantJobName,\n"+
//                "    sipwt.handle_person_name handlePersonName,sipwt.handle_person_code handlePersonCode,sipwt.handle_time handleTime,\n" +
                "sipw.id sipwId,"+
                "    smt.id smtId,\n" +
                "    ssis.settle_start_time settleStartTime,\n" +
                "    ssis.settle_end_time settleEndTime,\n" +
                "    ifnull(slps.SETTLEMENT_TERM,-1) settlementTerm,\n" +
                " (case 1 when datediff(ifnull(date_add(sls.END_SETTLE_DATE,interval 1 second),#{startSettleDate}) ,#{startSettleDate})>0 then date_add(sls.END_SETTLE_DATE,interval 1 second) else #{startSettleDate} end) as js_startSettleDate,\n" +
                "\t\t\t\tdate_add(scs.SETTLE_TIME,interval 93599 second) as js_endSettleDate, "+
                " #{endSettleDate} check_submit_end_date"+
                "    from sw_st_conductor_settle scs\n" +
                "    left join sw_st_input_passenger_work sipw on scs.line_code=sipw.line_code and scs.station_code=sipw.station_code and scs.conductor_job_no=sipw.conductor_code and sipw.confirm_status=1 and sipw.del_flag=0 and scs.SETTLE_TIME=sipw.WORK_DATE and scs.CONDUCTOR_SHIFTS=sipw.CLASS_NUMBER and  scs.DEVICE_OPERATE_NUM=sipw.DEVICE_OPERATE_NUM and scs.BOM_CODE = sipw.BOM_CODE\n" +
                "    left join sw_st_marketing_ticket smt on scs.line_code=smt.line_code and scs.station_code=smt.station_code and scs.conductor_job_no=smt.conductor_job_no  and smt.confirm_status=1 and smt.del_flag=0 and scs.CONDUCTOR_SHIFTS=smt.CONDUCTOR_SHIFTS and scs.SETTLE_TIME=smt.OPERTOR_TIME and  scs.DEVICE_OPERATE_NUM=smt.DEVICE_OPERATE_NUM and scs.BOM_CODE = smt.BOM_CODE\n" +
               //日志表关联BOM编号  ",设备操作号存储调整----待测试    
				//"  left join sw_st_longperiod_settle_log sls on scs.line_code=sls.line_code and scs.station_code=sls.station_code and scs.bom_code=sls.DEVICE_NO and sls.device_type=2 and scs.CONDUCTOR_JOB_NO = sls.CONDUCTOR_JOB_NO \n" +
				//
				"    left join sw_st_longperiod_settle_log sls on scs.line_code=sls.line_code and scs.station_code=sls.station_code and scs.bom_code=sls.DEVICE_NO and sls.device_type=2 and find_in_set(sls.CONDUCTOR_JOB_NO,scs.CONDUCTOR_JOB_NO) \n" +
                "    left join\n" +
                "            (select s.* from sw_pm_long_period_settlement s left join (select slps1.line_code,count(line_code) line_code_count\n" +
                "    from sw_pm_long_period_settlement slps1 where slps1.DEL_FLAG='0' and slps1.STATUS='1' GROUP BY line_code) slps2 on s.line_code=slps2.line_code and s.DEL_FLAG='0' and s.STATUS='1' where if(slps2.line_code_count > 1,\n" +
                "    s.STATION_CODE <>'' and s.STATION_CODE is not null ,1=1)) slps on if(slps.STATION_CODE <>'' and slps.STATION_CODE is not null,\n" +
                "    scs.LINE_CODE=slps.LINE_CODE and scs.STATION_CODE=slps.STATION_CODE,scs.LINE_CODE=slps.LINE_CODE)\n" +
                "\n" +
                "    left join sw_st_bom_income_settle ssis on scs.line_code=ssis.line_code and ssis.del_flag = '0' and scs.station_code=ssis.station_code and scs.bom_code=ssis.bom_code and \n" +
                "scs.SETTLE_TIME=ssis.RECORD_TIME and scs.CONDUCTOR_JOB_NO=ssis.CONDUCTOR_JOB_NO " +
                " and scs.DEVICE_OPERATE_NUM=ssis.DEVICE_OPERATOR_NUM ";
        //查询报表差错待提交时判断，已经加入报表差错的话不拼接班次
        if(StringUtils.isNotEmpty(param.get("isAddReportError")) && !"1".equals(param.get("isAddReportError"))){
            sql+= " and scs.CONDUCTOR_SHIFTS=ssis.CONDUCTOR_SHIFTS ";
        }
        String tj=" where scs.approval_status=0 and scs.confirm_status=1 and scs.del_flag=0 "+"and scs.IS_ADD_REPROT_ERROR="+param.get("isAddReportError") ;
        if(StringUtils.isNotEmpty(param.get("id"))){
            tj+=" and scs.id in ("+param.get("id")+") ";
        }
        if(StringUtils.isNotEmpty(param.get("ssisId"))){
            tj+=" and ssis.id in ("+param.get("ssisId")+") ";
        }
        if(StringUtils.isNotEmpty(param.get("lineCode"))){
            tj+=" and scs.line_code='"+param.get("lineCode")+"' ";
        }
        if(StringUtils.isNotEmpty(param.get("stationCode"))){
            tj+=" and scs.station_code='"+param.get("stationCode")+"' ";
        }
        if(StringUtils.isNotEmpty(param.get("preJieSuanDate"))){
            //tj+=" and (sls.end_settle_date='"+param.get("preJieSuanDate")+"' or  date_sub(scs.settle_time,interval 1 day)='"+param.get("preJieSuanDate")+"') ";
            tj+=" and  date_sub(scs.settle_time,interval 1 day)='"+param.get("preJieSuanDate")+"' ";
        }
        if(StringUtils.isNotEmpty(param.get("is_not_settle_status"))){
            tj+=" and (ssis.settle_status <> '"+param.get("is_not_settle_status")+"' or ssis.SETTLE_STATUS is null) ";
        }
        if(StringUtils.isNotEmpty(param.get("is_report_settle_status"))){
            tj+=" and ssis.settle_status = '"+param.get("is_report_settle_status")+"' ";
        }
        if(StringUtils.isNotEmpty(param.get("conductorName"))){
            tj+=" and  find_in_set('"+param.get("conductorName")+"',scs.CONDUCTOR_NAME) ";
        }
        if(StringUtils.isNotEmpty(param.get("settleTime"))){
            tj+=" and scs.settle_time = '"+param.get("settleTime")+"' ";
        }
        if(StringUtils.isNotEmpty(param.get("startSettleTime"))){
            tj+=" and scs.settle_time >= '"+param.get("startSettleTime")+"' ";
        }
        if(StringUtils.isNotEmpty(param.get("endSettleTime"))){
            tj+=" and scs.settle_time <= '"+param.get("endSettleTime")+"' ";
        }
        if(StringUtils.isNotEmpty(param.get("startSettleDate"))){
            tj+=" and DATE_ADD(scs.SETTLE_TIME,INTERVAL 12 HOUR) >= '"+param.get("startSettleDate")+"' ";
        }
        if(StringUtils.isNotEmpty(param.get("endSettleDate"))){
            tj+=" and DATE_ADD(scs.SETTLE_TIME,INTERVAL 12 HOUR) <= '"+param.get("endSettleDate")+"' ";
        }
        sql+=tj;

        sql=sql.replace("#{startSettleDate}","'"+param.get("startSettleDate")+"'");
        sql=sql.replace("#{endSettleDate}","'"+param.get("endSettleDate")+"'");
        List<Map<String, Object>> mapsList = JdbcUtil.exeSqlQuery(sql);
        
		//查询需要合并的售票员结算单,如果存在需要合并的，将其过滤掉
		String sqlSettle =  sqlSettle(param.get("lineCode"),param.get("stationCode"));
        List<Map<String, Object>> mapsSettle = JdbcUtil.exeSqlQuery(sqlSettle);
        List<Integer> lstIds = new ArrayList();
        if(CollectionUtil.isNotEmpty(mapsSettle) && mapsSettle.size()>0){
            for(int i=0;i<mapsSettle.size();i++){
                Map<String,Object> map = mapsSettle.get(i);
                lstIds.add(Integer.valueOf(String.valueOf(map.get("id"))));
            }
        }
        if(CollectionUtil.isNotEmpty(lstIds)){
            Iterator<Map<String, Object>> iterator = mapsList.iterator();
            while (iterator.hasNext()){
                Object id = iterator.next().get("id");
                if(lstIds.contains(id)){
                    iterator.remove();
                }
            }

        }
        return mapsList;
    }

    /**
     * 跟据线路站点查询售票员结算表
     * @param lineCode
     * @param stationCode
     * @return
     */
    static String sqlSettle(String lineCode, String stationCode) {
        String sqlSettle = "";
        if(ObjectUtil.isNotEmpty(lineCode) && ObjectUtil.isNotEmpty(stationCode)){
            sqlSettle = "select a.id from sw_st_conductor_settle a\n"+
                    "where \n"+
                    "line_code='"+lineCode+"' and station_code='"+stationCode+"' \n"+
                    "and del_flag='0' and CONFIRM_STATUS='1' and APPROVAL_STATUS='0' \n"+
                    "and is_merge='0' and SETTLE_TIME >= DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'),INTERVAL 30 DAY)\n"+
                    "and\n"+
                    "(a.device_operate_num,a.SETTLE_TIME) \n"+
                    "in (\n"+
                    "select device_operate_num,SETTLE_TIME \n"+
                    "from sw_st_conductor_settle \n"+
                    "where line_code='"+lineCode+"' and station_code='"+stationCode+"' \n"+
                    "and del_flag='0' and APPROVAL_STATUS='0' \n"+
                    "and is_merge='0' and SETTLE_TIME >= DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'),INTERVAL 30 DAY)\n"+
                    "group by device_operate_num,SETTLE_TIME having count(*) > 1\n"+
                    ") ";
        }else {
            sqlSettle = "select a.id, a.LINE_CODE, a.STATION_CODE from sw_st_conductor_settle a\n"+
                    "where \n"+
                    "del_flag='0' and CONFIRM_STATUS='1' and APPROVAL_STATUS='0' \n"+
                    "and is_merge='0' and SETTLE_TIME >= DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'),INTERVAL 30 DAY) \n"+
                    "and\n"+
                    "(a.line_code, a.station_code, a.device_operate_num, a.SETTLE_TIME) \n"+
                    "in (\n"+
                    "select line_code, station_code, device_operate_num,SETTLE_TIME \n"+
                    "from sw_st_conductor_settle \n"+
                    "where \n"+
                    "del_flag='0' and APPROVAL_STATUS='0' \n"+
                    "and is_merge='0' and SETTLE_TIME >= DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'),INTERVAL 30 DAY) \n"+
                    "group by line_code, station_code, device_operate_num,SETTLE_TIME having count(*) > 1\n"+
                    ") ";
        }


        return sqlSettle;
    }



  /**
  
     乘客事务金额只查现金事务和故障退款金额  以及其他相关数据录入
	 故障退款金额一定是负数  sw_st_input_passenger_work_refund
	 现金事务有正数和负数    sw_st_input_passenger_work_cash 
	 其他相关数据录入负数  sw_st_input_passenger_work_other 数据类型为特殊情况退票、正常情况退票 2和3两种情况  20210506增加
                            2021年7月29日去掉，统一算到应急纸票 sw_st_input_passenger_work_other 数据类型为发售应急纸票 1， 因大客流发售前往其他运营商应急纸票，以及因大客流发售西安地铁应急纸票  对应产生原因是2和5
	 正负数求和后，由实收金额进行减法操作。

	 原则是正减负加
  */
   public static List<Map<String, Object>> getPassengerWorkAmount(JSONObject param) throws SQLException, ParseException {
        String relation_id=String.valueOf(param.get("sipwId"));//乘客事务id
      
	   String lineCode=param.getString("lineCode");
       String stationCode=param.getString("stationCode");
       String conductorJobNo = param.getString("conductorJobNo");
       Date startTime = param.getDate("startTime");
       Date endTime = param.getDate("endTime");
       String startTimeStr = DateFormat.date_ex_str(startTime, "yyyy-MM-dd HH:mm:ss");
       String endTimeStr = DateFormat.date_ex_str(endTime, "yyyy-MM-dd HH:mm:ss");
       String conductorShifts = param.getString("conductorShifts");
	   String deviceOperateNum = param.getString("deviceOperateNum");
	  
	   String sql="SELECT ifnull(sum(AMOUNT),0) amount from (\n" +//*100
                " select sum(swc.AMOUNT) AMOUNT from sw_st_input_passenger_work_cash swc where swc.DEL_FLAG='0' and swc.RELATION_ID=" +relation_id+
                " union all\n" +
                " select sum(swr.AMOUNT_REFUNDED) AMOUNT from sw_st_input_passenger_work_refund swr where swr.RELATION_ID=" +relation_id+
                 " union all \n" +
                " SELECT sum(swo.AMOUNT) AMOUNT from sw_st_input_passenger_work_other swo where swo.DATA_TYPE in('2','3') and swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and DEVICE_OPERATE_NUM='"+deviceOperateNum+"'  and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'"+               
                ") a\n";			
               
       /*
	    String sql="SELECT ifnull(sum(AMOUNT),0) amount from (\n" +//*100
                " select sum(swc.AMOUNT) AMOUNT from sw_st_input_passenger_work_cash swc where swc.DEL_FLAG='0' and swc.RELATION_ID=" +relation_id+
                " union all\n" +
                " select sum(swr.AMOUNT_REFUNDED) AMOUNT from sw_st_input_passenger_work_refund swr where swr.RELATION_ID=" +relation_id+
                 " union all \n" +
                " SELECT sum(swo.AMOUNT) AMOUNT from sw_st_input_passenger_work_other swo where swo.DATA_TYPE in('2','3') and swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and DEVICE_OPERATE_NUM='"+deviceOperateNum+"'  and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'"+
                " union all \n" +
                " SELECT sum(swo.AMOUNT) AMOUNT from sw_st_input_passenger_work_other swo where swo.DATA_TYPE='1' and HAPPEN_CAUSE in('2','5') and swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and DEVICE_OPERATE_NUM='"+deviceOperateNum+"'  and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'"+
                ") a\n";	
       String sql="SELECT ifnull(sum(AMOUNT),0) amount from (\n" +//*100
                " select sum(swc.AMOUNT) AMOUNT from sw_st_input_passenger_work_cash swc where swc.DEL_FLAG='0' and swc.RELATION_ID=" +relation_id+
                " union all\n" +
                " select sum(swe.AMOUNT) AMOUNT from sw_st_input_passenger_work_electron swe where swe.DEL_FLAG='0' and swe.RELATION_ID=" +relation_id+
                " union all\n" +
                " select sum(swp.AMOUNT) AMOUNT from sw_st_input_passenger_work_pay swp where swp.DEL_FLAG='0' and swp.RELATION_ID=" +relation_id+
                " union all\n" +
                " select sum(swr.AMOUNT_REFUNDED) AMOUNT from sw_st_input_passenger_work_refund swr where swr.RELATION_ID=" +relation_id+
                " union all \n" +
                " SELECT sum(swt.AMOUNT) AMOUNT from sw_st_input_passenger_work_ticket swt where swt.LINE_CODE='"+lineCode+"' and swt.STATION_CODE='"+stationCode+"' and swt.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swt.HANDLE_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'"+
                " union all \n" +
                " SELECT sum(swo.AMOUNT) AMOUNT from sw_st_input_passenger_work_other swo where swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"'and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'"+
                ") a\n";
	   */
       List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
       return maps;
    }
    /*
	 * 开-关-废票 -其他相关数据录入， 数据类型为1 发售应急纸票  产生原因为误出站发售应急纸票 1 20210729
	 获取应急纸票金额  数据类型为1 发售应急纸票 1  产生原因为  产生原因为误出站发售应急纸票，具体标识待定----TODO
	*/
	 public static BigDecimal getEmergencyAmount(JSONObject param) throws SQLException, ParseException {   
	   String lineCode=param.getString("lineCode");
       String stationCode=param.getString("stationCode");
       String conductorJobNo = param.getString("conductorJobNo");
       Date startTime = param.getDate("startTime");
       Date endTime = param.getDate("endTime");
       String startTimeStr = DateFormat.date_ex_str(startTime, "yyyy-MM-dd HH:mm:ss");
       String endTimeStr = DateFormat.date_ex_str(endTime, "yyyy-MM-dd HH:mm:ss");
       String conductorShifts = param.getString("conductorShifts");
	   String deviceOperateNum = param.getString("deviceOperateNum");
	   return getEmergencyByCondition(lineCode,stationCode,conductorJobNo,startTimeStr,endTimeStr,conductorShifts, deviceOperateNum,"");
	 
	 }
	public static BigDecimal getEmergencyAmountByMap(Map param) throws SQLException, ParseException {       
      
	   String lineCode=String.valueOf(param.get("lineCode"));
       String stationCode=String.valueOf(param.get("stationCode"));
       String conductorJobNo = String.valueOf(param.get("conductorJobNo"));      
       String startTimeStr = String.valueOf(param.get("startTime"));
       String endTimeStr = String.valueOf(param.get("endTime"));
       String conductorShifts = String.valueOf(param.get("conductorShifts"));
	   String deviceOperateNum = String.valueOf(param.get("deviceOperateNum"));
	   return getSingleTicketAmountByCondition(lineCode,stationCode,conductorJobNo,startTimeStr,endTimeStr,conductorShifts, deviceOperateNum,"");
    }

    /**
     * 查询应急纸票金额
     * @param lineCode
     * @param stationCode
     * @param conductorJobNo
     * @param startTimeStr
     * @param endTimeStr
     * @param conductorShifts
     * @param deviceOperateNum
     * @param settleDate
     * @return
     * @throws SQLException
     * @throws ParseException
     */
	 private  static  BigDecimal  getEmergencyByCondition(String lineCode,String stationCode,String conductorJobNo,String startTimeStr,String endTimeStr,String conductorShifts, String deviceOperateNum,String settleDate) throws SQLException, ParseException {   
	    BigDecimal cashRes=new BigDecimal(0);
	  
	   String sqlPetty ="SELECT sum((a.WINDOWS_NUM-a.WINDOWS_CLOSE_NUM -a.INVALID_TICKET_NUM -a.POS_SALE_NUM)*a.TICKET_VALUE) amount "+
						" from sw_st_ticket_petty_add_details  a,sw_st_ticket_petty_add b "+
						" where a.relation_id=b.id "+
						" and a.del_flag='0' "+
						" and b.del_flag='0' "+
						" and a.type='2' "+
						" and b.confirm_status='1' "+
		                " and a.ticket_name='YJZP' "+
						" and b.line_code='"+lineCode+"' "+
						" and b.station_code='"+stationCode+"' "+
						" and b.ns_job_no='"+conductorJobNo+"' "+  
						" and b.ns_shift='"+conductorShifts+"' "+
						" and b.device_operate_num='"+deviceOperateNum+"' "+						
						" and b.WORKING_END_TIME between '"+startTimeStr+"' and '"+endTimeStr+"'";
	    
		String sqlTicketOther = "SELECT ifnull(sum(abs(AMOUNT)),0) amount from sw_st_input_passenger_work_other swo \n"+
	                             " where swo.DATA_TYPE='1' and swo.HAPPEN_CAUSE in('1') \n"+
			                     " and swo.del_flag='0' and swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"'  \n" +
                                " and swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and swo.DEVICE_OPERATE_NUM='"+deviceOperateNum+"'  and swo.CLASS_NUMBER='" +conductorShifts+"' ";
        String sql = "select ("+"("+sqlPetty+")-("+sqlTicketOther+")) amount from dual";
	    List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
	    if(maps!=null && maps.size()>0){
            Map<String, Object> res = maps.get(0);
            cashRes = (BigDecimal)res.get("amount");
            if(cashRes==null){
                cashRes = new BigDecimal("0.00");
            }
        }
       return cashRes;
	 
	 }
   /*
	 * 获取单程票金额 从配票明细表中查找 开-关的预制单程票金额  以及去掉乘客事务退换票中即时退换票票种是预制单程票，类型是换票的金额
	*/
	 public static BigDecimal getSingleTicketAmount(JSONObject param) throws SQLException, ParseException {       
      
	   BigDecimal cashRes=new BigDecimal(0);
	   String lineCode=param.getString("lineCode");
       String stationCode=param.getString("stationCode");
       String conductorJobNo = param.getString("conductorJobNo");
       Date startTime = param.getDate("startTime");
       Date endTime = param.getDate("endTime");
       String startTimeStr = DateFormat.date_ex_str(startTime, "yyyy-MM-dd HH:mm:ss");
       String endTimeStr = DateFormat.date_ex_str(endTime, "yyyy-MM-dd HH:mm:ss");
       String conductorShifts = param.getString("conductorShifts");
	   String deviceOperateNum = param.getString("deviceOperateNum");
	   return getSingleTicketAmountByCondition(lineCode,stationCode,conductorJobNo,startTimeStr,endTimeStr,conductorShifts, deviceOperateNum,"");
    }

    /**
     * 获取单程票金额
     * @param lineCode
     * @param stationCode
     * @param conductorJobNo
     * @param startTimeStr
     * @param endTimeStr
     * @param conductorShifts
     * @param deviceOperateNum
     * @param settleDate
     * @return
     * @throws SQLException
     * @throws ParseException
     */
	 private  static  BigDecimal  getSingleTicketAmountByCondition(String lineCode,String stationCode,String conductorJobNo,String startTimeStr,String endTimeStr,String conductorShifts, String deviceOperateNum,String settleDate) throws SQLException, ParseException {   
	      BigDecimal cashRes=new BigDecimal(0);
		  String sqlPetty ="SELECT sum((a.WINDOWS_NUM-a.WINDOWS_CLOSE_NUM -a.INVALID_TICKET_NUM - a.POS_SALE_NUM)*a.TICKET_VALUE) amount "+
						" from sw_st_ticket_petty_add_details  a,sw_st_ticket_petty_add b "+
						" where a.relation_id=b.id "+
						" and a.del_flag='0' "+
						" and b.del_flag='0' "+
						" and a.type='2' "+
						" and b.confirm_status='1' "+
		                " and a.ticket_name='YZDCP' "+
						" and b.line_code='"+lineCode+"' "+
						" and b.station_code='"+stationCode+"' "+
						" and b.ns_job_no='"+conductorJobNo+"' "+  
						" and b.ns_shift='"+conductorShifts+"' "+
						" and b.device_operate_num='"+deviceOperateNum+"' "+						
						" and b.WORKING_END_TIME between '"+startTimeStr+"' and '"+endTimeStr+"'";
	                    //" and (CASE WHEN HOUR(b.WORKING_END_TIME)<2 then DATE_FORMAT(date_sub(b.WORKING_END_TIME,INTERVAL 2 hour),'%Y-%m-%d') else DATE_FORMAT(b.WORKING_END_TIME,'%Y-%m-%d') end)='"+param.get("recordTime")+"'";
        String sqlTicketOther = "select ifnull(sum(a.TICKET_PRICE),0) as amount from sw_st_input_passenger_work_ticket a\n"+
						"where  a.OPERATION_TYPE='0'\n"+
						"and a.REPLACE_TICKET_TYPE='YZDCP'\n"+
						"and a.DEL_FLAG='0'\n"+
						"and a.LINE_CODE='"+lineCode+"'\n"+
						"and a.STATION_CODE='"+stationCode+"'\n"+
						"and a.HANDLE_TYPE='1'\n"+
						"and DATE_FORMAT(a.HANDLE_TIME,'%Y-%m-%d') = '"+startTimeStr.substring(0,10)+"'\n"+
						"and a.CONFIRM_STATUS='1'\n"+
						"and a.HANDLE_PERSON_CODE='"+conductorJobNo+"'\n"+
						"and a.DEVICE_OPERATE_NUM='"+deviceOperateNum+"'\n"+
						"and a.CLASS_NUMBER='"+conductorShifts+"'";
        String sql = "select ("+"("+sqlPetty+")-("+sqlTicketOther+")) amount from dual";
		List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
	    if(maps!=null && maps.size()>0){
            Map<String, Object> res = maps.get(0);
            cashRes = (BigDecimal)res.get("amount");
            if(cashRes==null){
                cashRes = new BigDecimal("0.00");
            }
        }
       return cashRes;
	 
	 }
	 public static BigDecimal getSingleTicketAmountByMap(Map param) throws SQLException, ParseException {       
      
	   BigDecimal cashRes=new BigDecimal(0);
	   String lineCode=String.valueOf(param.get("lineCode"));
       String stationCode=String.valueOf(param.get("stationCode"));
       String conductorJobNo = String.valueOf(param.get("conductorJobNo"));      
       String startTimeStr = String.valueOf(param.get("startTime"));
       String endTimeStr = String.valueOf(param.get("endTime"));
       String conductorShifts = String.valueOf(param.get("conductorShifts"));
	   String deviceOperateNum = String.valueOf(param.get("deviceOperateNum"));
	   return getSingleTicketAmountByCondition(lineCode,stationCode,conductorJobNo,startTimeStr,endTimeStr,conductorShifts, deviceOperateNum,"");
    }

    /**
     * 判断是否有无乘客事务
     * @param param
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    public static Integer isHaveBus(JSONObject param) throws SQLException, ParseException {
        String relation_id=String.valueOf(param.get("sipwId"));//乘客事务id
        String lineCode=param.getString("lineCode");
        String stationCode=param.getString("stationCode");
        String conductorJobNo = param.getString("conductorJobNo");
        Date startTime = param.getDate("startTime");
        Date endTime = param.getDate("endTime");
        String startTimeStr = DateFormat.date_ex_str(startTime, "yyyy-MM-dd HH:mm:ss");
        String endTimeStr = DateFormat.date_ex_str(endTime, "yyyy-MM-dd HH:mm:ss");
        String conductorShifts = param.getString("conductorShifts");
		String deviceOperateNum = param.getString("deviceOperateNum");

        String sql1="SELECT * from sw_st_input_passenger_work_ticket swt where swt.LINE_CODE='"+lineCode+"' and swt.STATION_CODE='"+stationCode+"' and swt.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swt.HANDLE_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and swt.DEVICE_OPERATE_NUM='"+deviceOperateNum+"' and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'";

        String sql2=" SELECT * from sw_st_input_passenger_work_other swo where swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and swo.DEVICE_OPERATE_NUM='"+deviceOperateNum+"'  and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'";
        List<Map<String, Object>> maps1 = JdbcUtil.exeSqlQuery(sql1);
        List<Map<String, Object>> maps2 = JdbcUtil.exeSqlQuery(sql2);
        if((StringUtils.isEmpty(relation_id) || "null".equals(relation_id) || "NULL".equals(relation_id)) && maps1.size()==0 && maps2.size()==0){
            return 0; //空，或者数量为0就无乘客无乘客事务
        }
        return 1;
    }

    /**
     * 判断是否有无营销车票
     * @param param
     * @return
     * @throws SQLException
     * @throws ParseException
     */
	  public static Integer isHaveMarket(JSONObject param) throws SQLException, ParseException {       
        String lineCode=param.getString("lineCode");
        String stationCode=param.getString("stationCode");
        String conductorJobNo = param.getString("conductorJobNo");
        Date startTime = param.getDate("startTime");
        Date endTime = param.getDate("endTime");
        String startTimeStr = DateFormat.date_ex_str(startTime, "yyyy-MM-dd HH:mm:ss");
        String endTimeStr = DateFormat.date_ex_str(endTime, "yyyy-MM-dd HH:mm:ss");
        String conductorShifts = param.getString("conductorShifts");
		String deviceOperateNum = param.getString("deviceOperateNum");
		String bomCode = param.getString("bomCode");
		String settleTime = param.getString("settleTime");
        String sql1="SELECT * from sw_st_marketing_ticket swt where swt.CONFIRM_STATUS='1' and  swt.LINE_CODE='"+lineCode+"' and swt.STATION_CODE='"+stationCode+"' and swt.CONDUCTOR_JOB_NO='"+conductorJobNo+"' and \n" +
                " swt.OPERTOR_TIME='"+settleTime+"' and swt.DEVICE_OPERATE_NUM='"+deviceOperateNum+"' and swt.BOM_CODE='"+bomCode+"'  and CONDUCTOR_SHIFTS='" +conductorShifts+"' and DEL_FLAG='0'";
      
        List<Map<String, Object>> maps1 = JdbcUtil.exeSqlQuery(sql1);
     
        if(maps1==null || maps1.size()==0 ){
            return 0; //空，或者数量为0就无营销车票
        }
        return 1;
    }
	/*
	  
	  获取营销车票现金部分金额
	
	*/
    public static BigDecimal getMarketTicketAmount(JSONObject param) throws SQLException, ParseException {

        SwStMarketingTicketGroupSellMapper swStMarketingTicketGroupSellMapper=SpringContextHolder.getBean(SwStMarketingTicketGroupSellMapper.class);

        SwStMarketingTicketOffineSellMapper swStMarketingTicketOffineSellMapper=SpringContextHolder.getBean(SwStMarketingTicketOffineSellMapper.class);

        String lineCode=param.getString("lineCode");
        String stationCode=param.getString("stationCode");
        String settleTime = param.getString("settleTime");
        String conductorJobNo = param.getString("conductorJobNo");
        String conductorShifts = param.getString("conductorShifts");
        Boolean isReport=param.getBoolean("isReport");isReport=isReport==null?false:isReport;
        String sql="SELECT id as smtId from SW_ST_MARKETING_TICKET where DEL_FLAG='0' " +
                " and LINE_CODE ='" +lineCode+"'"+
                " and STATION_CODE = '" +stationCode+"'"+
                " and OPERTOR_TIME = '" +settleTime+"'"+
                " and CONDUCTOR_JOB_NO = '" +conductorJobNo+"'"+
                " and CONDUCTOR_SHIFTS = '" +conductorShifts+"'";
                /*if(!isReport){
                    sql+= " and APPROVAL_STATUS = '0' " ;
                }*/
        sql+=" and CONFIRM_STATUS = '1'";
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQueryByCon(sql);
        String smtId="0";
        if(maps.size()>0){
            smtId=String.valueOf(maps.get(0).get("smtId"));
        }
        BigDecimal cashRes=new BigDecimal(0);
        QueryWrapper<SwStMarketingTicketGroupSell> swStMarketingTicketGroupSellQueryWrapper=new QueryWrapper<>();
        swStMarketingTicketGroupSellQueryWrapper.select("ifnull(sum(discount_amount),0) as discount_amount");//*100
        swStMarketingTicketGroupSellQueryWrapper.eq("relation_id",smtId);
        swStMarketingTicketGroupSellQueryWrapper.eq("del_flag",0);
        String[] pay_method={"0"}; //现金
        swStMarketingTicketGroupSellQueryWrapper.in("pay_method",pay_method);
        List<Map<String, Object>> groupMaps = swStMarketingTicketGroupSellMapper.selectMaps(swStMarketingTicketGroupSellQueryWrapper);//团购的
        if(groupMaps!=null && groupMaps.size()>0){
            Map<String, Object> res = groupMaps.get(0);
            BigDecimal discount_amount = (BigDecimal)res.get("discount_amount");
            cashRes=cashRes.add(discount_amount);
        }

        QueryWrapper<SwStMarketingTicketOffineSell> swStMarketingTicketOffineSellQueryWrapper=new QueryWrapper<>();
        swStMarketingTicketOffineSellQueryWrapper.select("ifnull(sum(CASH_SELL_AMOUNT),0) as cash_sell_amount");//*100
        swStMarketingTicketOffineSellQueryWrapper.eq("relation_id",smtId);
        swStMarketingTicketOffineSellQueryWrapper.eq("del_flag",0);        
        List<Map<String, Object>> map2 = swStMarketingTicketOffineSellMapper.selectMaps(swStMarketingTicketOffineSellQueryWrapper);//现金
        if(map2!=null && map2.size()>0){
            Map<String, Object> res = map2.get(0);
            BigDecimal cash_sell_amount = (BigDecimal)res.get("cash_sell_amount");
            cashRes=cashRes.add(cash_sell_amount);
            if(cashRes == null){
                cashRes = new BigDecimal("0.00");
            }
        }

        return cashRes;
    }


    /**
     * 获取sc,ls,acc
     * @param param
     * @return
     * @throws SQLException
     */
    public static Map<String,BigDecimal> getScLcAcc(Map<String,Object> param) throws SQLException, ParseException {
       Object lineCode=param.get("lineCode");
        Object stationCode=param.get("stationCode");
        String type = "2";
        Object bomCode= param.get("bomCode");
        String deviceOperateNum= (String)param.get("deviceOperateNum");
        if(StringUtils.isEmpty(deviceOperateNum)){
            deviceOperateNum= (String)param.get("equipmentNumber");
        }
        bomCode=bomCode.toString().replace(",","','");
        deviceOperateNum=deviceOperateNum.replace(",","','");
        Object startTime = param.get("startTime");
        Object endTime = param.get("endTime");

        List<Map<String, Object>> scMap = JdbcUtil.exeSqlQuery("select \n" +
                "ifnull(sum(sale_txnamtall),0) as sc_sell,  \n" +
                "ifnull(sum(recharge_txnamtall),0) as sc_de ,\n" +
                "ifnull(sum(refund_txnamtall),0) as sc_return, \n" +
                "ifnull(sum(supplement_txnamtall),0) as sc_su \n" +
                "from sc_fare_statistics \n" +
                "where equipmenttype='"+type+"'\n" +
                "and station_equipment_number in ('"+bomCode+"')\n" +
                "and posoperid in ('"+deviceOperateNum+"') "+
                " and (paymethod='0' or paymethod='1' or paymethod='10' or paymethod='255' OR paymethod is NULL or paymethod like '%3130%')\n" +  //增加paymethod is null的情况
               	"and lineid='"+lineCode+"'\n" +
                "and station_number='"+stationCode+"'\n" +
                "and bookdate >='"+startTime+"' and bookdate <= '"+endTime+"'"
                , "sc_sell,sc_de,sc_return");

        List<Map<String, Object>> lcMap = JdbcUtil.exeSqlQuery("select \n" +
                "ifnull(sum(sale_txnamtall),0) as lc_sell,  \n" +
                "ifnull(sum(recharge_txnamtall),0) as lc_de ,\n" +
                "ifnull(sum(refund_txnamtall),0) as lc_return, \n" +
                "ifnull(sum(supplement_txnamtall),0) as lc_su \n" +
                "from lc_fare_statistics \n" +
                "where equipmenttype='"+type+"'\n" +
                 "and station_equipment_number in ('"+bomCode+"')\n" +
                 "and posoperid in ('"+deviceOperateNum+"') "+
               " and (paymethod='0' or paymethod='1' or paymethod='10' or paymethod='255' OR paymethod is NULL or paymethod like '%3130%')\n" +  //增加paymethod is null的情况
             	"and lineid='"+lineCode+"'\n" +
                "and station_number='"+stationCode+"'\n" +
                "and bookdate >='"+startTime+"' and bookdate <= '"+endTime+"'"
                , "lc_sell,lc_de,lc_return");

        List<Map<String, Object>> accMap = JdbcUtil.exeSqlQuery("select \n" +
                "ifnull(sum(sale_txnamtall),0) as acc_sell,  \n" +
                "ifnull(sum(recharge_txnamtall),0) as acc_de ,\n" +
                "ifnull(sum(refund_txnamtall),0) as acc_return ,\n" +
                "ifnull(sum(supplement_txnamtall),0) as acc_su \n" +
                "from acc_fare_statistics \n" +
                "where equipmenttype in ('2','7')\n" + //6号线数据是7  20221230
                "and station_equipment_number in ('"+bomCode+"')\n" +
                "and posoperid in ('"+deviceOperateNum+"') "+
                " and (paymethod='0' or paymethod='1' or paymethod='10' or paymethod='255' OR paymethod is NULL or paymethod like '%3130%')\n" +   //增加paymethod is null的情况
              	"and lineid='"+lineCode+"'\n" +
                "and station_number='"+stationCode+"'\n" +
//                "and bookdate='"+revenueTime+"'"
                "and bookdate >='"+startTime+"' and bookdate <= '"+endTime+"'"
                , "acc_sell,acc_de,acc_return");

        Map<String,BigDecimal> res=new HashMap<>();
        if(scMap.size()>0){
            res.put("sc_sell",(BigDecimal)scMap.get(0).get("sc_sell"));
            res.put("sc_de",(BigDecimal)scMap.get(0).get("sc_de"));
            res.put("sc_return",(BigDecimal)scMap.get(0).get("sc_return"));
            res.put("sc_su",(BigDecimal)scMap.get(0).get("sc_su"));
        }else{
            res.put("sc_sell",new BigDecimal(0));
            res.put("sc_de",new BigDecimal(0));
            res.put("sc_return",new BigDecimal(0));
            res.put("sc_su",(BigDecimal)scMap.get(0).get("sc_su"));
        }

        if(lcMap.size()>0){
            res.put("lc_sell",(BigDecimal)lcMap.get(0).get("lc_sell"));
            res.put("lc_de",(BigDecimal)lcMap.get(0).get("lc_de"));
            res.put("lc_return",(BigDecimal)lcMap.get(0).get("lc_return"));
            res.put("lc_su",(BigDecimal)lcMap.get(0).get("lc_su"));
        }else{
            res.put("lc_sell",new BigDecimal(0));
            res.put("lc_de",new BigDecimal(0));
            res.put("lc_return",new BigDecimal(0));
            res.put("lc_su",(BigDecimal)lcMap.get(0).get("lc_su"));
        }

        if(accMap.size()>0){
            res.put("acc_sell",(BigDecimal)accMap.get(0).get("acc_sell"));
            res.put("acc_de",(BigDecimal)accMap.get(0).get("acc_de"));
            res.put("acc_return",(BigDecimal)accMap.get(0).get("acc_return"));
            res.put("acc_su",(BigDecimal)accMap.get(0).get("acc_su"));
        }else{
            res.put("acc_sell",new BigDecimal(0));
            res.put("acc_de",new BigDecimal(0));
            res.put("acc_return",new BigDecimal(0));
            res.put("acc_su",(BigDecimal)accMap.get(0).get("acc_su"));
        }
        return res;
    }

    /**
     * 弹框提示使用
     * @param param
     * @return
     * @throws SQLException
     */
    public static JSONObject getBomNotConfirmData(Map<String,Object> param) throws SQLException {
        Object lineCode = param.get("lineCode");
        Object stationCode = param.get("stationCode");
        String sql="select * from sw_st_conductor_settle scs where scs.approval_status=0 and scs.confirm_status=0 and scs.del_flag=0 " +
                "and line_code='"+lineCode+"' and station_code='"+stationCode+"'";
            sql+=" and DATE_ADD(scs.SETTLE_TIME,INTERVAL 12 HOUR) >= '"+param.get("startSettleDate")+"' ";
            sql+=" and DATE_ADD(scs.SETTLE_TIME,INTERVAL 12 HOUR) <= '"+param.get("endSettleDate")+"' ";
       
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);

        Integer beiYongJins=0;//配票
        Integer yingXioaChePiaos=0;//营销车票
        Integer cksws=0;//乘客事务
        Integer qtxgsj=0;//其他相关数据录入
        Integer ckswthp=0;//乘客事务退换票

        //车站配票与配备用金
		String beiYongJinSql="select a.* from sw_st_ticket_petty_add a,sw_st_ticket_petty_add_details b  where a.id=b.relation_id and  a.del_flag=0 and a.confirm_status=0 "+
                    " and b.del_flag='0' and b.type='2'  and a.line_code='"+lineCode+ "'" +
                    " and a.station_code='"+stationCode+ "'" +
			        " and date_format(a.WORKING_END_TIME,'%Y-%m-%d') >= '"+param.get("startSettleDate")+"' "+
                    " and date_format(a.WORKING_END_TIME,'%Y-%m-%d') <= '"+param.get("endSettleDate")+"' ";
        List<Map<String, Object>> beiYongJinSql_res = JdbcUtil.exeSqlQuery(beiYongJinSql); //配票
        beiYongJins=beiYongJinSql_res.size();

        //营销车票
		String yingXioaChePiao="select * from sw_st_marketing_ticket where del_flag=0 and confirm_status=0" +
                    " and line_code='"+lineCode+ "'" +
                    " and station_code='"+stationCode+ "'" +                
                    " and OPERTOR_TIME >= '"+param.get("startSettleDate")+"' "+
                    " and OPERTOR_TIME <= '"+param.get("endSettleDate")+"' ";
        List<Map<String, Object>> yingXioaChePiao_res = JdbcUtil.exeSqlQuery(yingXioaChePiao);//营销车票
        yingXioaChePiaos=yingXioaChePiao_res.size();

        //乘客事务录入
        String cksw_sql="select * from sw_st_input_passenger_work where del_flag=0 and confirm_status=0" +
                    " and line_code='"+lineCode+ "'" +
                    " and station_code='"+stationCode+ "'"+
		            " and WORK_DATE >= '"+param.get("startSettleDate")+"' "+
                    " and WORK_DATE <= '"+param.get("endSettleDate")+"' ";
        List<Map<String, Object>> cksw_res = JdbcUtil.exeSqlQuery(cksw_sql);
        cksws=cksw_res.size();

        //其他相关数据录入
        String qtxgsj_sql="select * from sw_st_input_passenger_work_other where del_flag=0 and CONFIRM_STATUS=0" +
                " and line_code='"+lineCode+ "'" +
                " and station_code='"+stationCode+ "'"+
                " and INPUT_TIME >= '"+param.get("startSettleDate")+"' "+
                " and INPUT_TIME <= '"+param.get("endSettleDate")+"' ";
        List<Map<String, Object>> qtxgsj_res = JdbcUtil.exeSqlQuery(qtxgsj_sql);
        qtxgsj=qtxgsj_res.size();

        //乘客事务退换票
        String ckswthp_sql="select * from sw_st_input_passenger_work_ticket where del_flag=0 and CONFIRM_STATUS=0" +
                " and line_code='"+lineCode+ "'" +
                " and station_code='"+stationCode+ "'"+
                " and HANDLE_TIME >= '"+param.get("startSettleDate")+"' "+
                " and HANDLE_TIME <= '"+param.get("endSettleDate")+"' ";
        List<Map<String, Object>> ckswthp_res = JdbcUtil.exeSqlQuery(ckswthp_sql);
        ckswthp=ckswthp_res.size();


        /*
		for(Map<String, Object> map:maps){
            Object line_code = map.get("line_code");
            Object station_code = map.get("station_code");
            Object ns_job_no = map.get("conductor_job_no");
            Object ns_shift = map.get("conductor_shifts");
            Object deviceOperateNum = map.get("device_operate_num");
            Object bomCode = map.get("bomCode");
            Object settleTime = map.get("settle_time");
            String beiYongJinSql="select a.* from sw_st_ticket_petty_add a,sw_st_ticket_petty_add_details b  where a.id=b.relation_id and  a.del_flag=0 and a.confirm_status=0 "+
                    " and b.del_flag='0' and b.type='2'  and a.line_code='"+line_code+ "'" +
                    " and a.station_code='"+station_code+ "'" +
                    " and a.NS_JOB_NO='"+ns_job_no+ "'" +
                    " and a.NS_SHIFT='"+ns_shift+ "'" +
                    " and a.DEVICE_OPERATE_NUM ='"+deviceOperateNum+ "'";
            List<Map<String, Object>> beiYongJinSql_res = JdbcUtil.exeSqlQuery(beiYongJinSql); //配票
            beiYongJins+=beiYongJinSql_res.size();

            String yingXioaChePiao="select * from sw_st_marketing_ticket where del_flag=0 and confirm_status=0" +
                    " and line_code='"+line_code+ "'" +
                    " and station_code='"+station_code+ "'" +
                    " and CONDUCTOR_JOB_NO='"+ns_job_no+ "'" +
                    " and CONDUCTOR_SHIFTS='"+ns_shift+ "'" +
                    " and OPERTOR_TIME='"+settleTime+"'";
            List<Map<String, Object>> yingXioaChePiao_res = JdbcUtil.exeSqlQuery(yingXioaChePiao);//营销车票
            yingXioaChePiaos+=yingXioaChePiao_res.size();

            String cksw_sql="select * from sw_st_input_passenger_work where del_flag=0 and confirm_status=0" +
                    " and line_code='"+line_code+ "'" +
                    " and station_code='"+station_code+ "'" +
                    " and CONDUCTOR_CODE='"+ns_job_no+ "'" +
                    " and CLASS_NUMBER='"+ns_shift+ "'" +
                    " and WORK_DATE='"+settleTime+"'";
            List<Map<String, Object>> cksw_res = JdbcUtil.exeSqlQuery(cksw_sql);
            cksws+=cksw_res.size();
        }*/

        JSONObject res=new JSONObject();
        res.put("beiYongJins",beiYongJins);
        res.put("yingXioaChePiaos",yingXioaChePiaos);
        res.put("cksws",cksws);
        res.put("qtxgsj",qtxgsj);
        res.put("ckswthp",ckswthp);
        res.put("spyrjs",maps.size());
        return res;
    }
    /*获取营销车票现金部分金额*/
    public static BigDecimal getMarketTicketAmountRepair(JSONObject param) throws SQLException, ParseException {
        //使用SpringContextHolder获取bean实例
        SwStMarketingTicketGroupSellMapper swStMarketingTicketGroupSellMapper=SpringContextHolder.getBean(SwStMarketingTicketGroupSellMapper.class);
        SwStMarketingTicketOffineSellMapper swStMarketingTicketOffineSellMapper=SpringContextHolder.getBean(SwStMarketingTicketOffineSellMapper.class);
        String lineCode=param.getString("lineCode");
        String stationCode=param.getString("stationCode");
        String settleTime = param.getString("settleTime");
        String conductorJobNo = param.getString("conductorJobNo");
        String bomCode = param.getString("bomCode");
        String conductorShifts = param.getString("conductorShifts");
        String sql="SELECT id as smtId from SW_ST_MARKETING_TICKET where DEL_FLAG='0'" +
                " and LINE_CODE ='" +lineCode+"'"+
                " and STATION_CODE = '" +stationCode+"'"+
                " and OPERTOR_TIME = '" +settleTime+"'"+
                " and CONDUCTOR_JOB_NO = '" +conductorJobNo+"'"+
                " and CONDUCTOR_SHIFTS = '" +conductorShifts+"'"+
                " and BOM_CODE = '" +bomCode+"'"+
                " and CONFIRM_STATUS = '1'";
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQueryByCon(sql);
        String smtId="0";
        if(maps.size()>0){
            smtId=String.valueOf(maps.get(0).get("smtId"));
        }
        BigDecimal cashRes=new BigDecimal(0);
        QueryWrapper<SwStMarketingTicketGroupSell> swStMarketingTicketGroupSellQueryWrapper=new QueryWrapper<>();
        swStMarketingTicketGroupSellQueryWrapper.select("ifnull(sum(discount_amount),0) as discount_amount");//*100
        swStMarketingTicketGroupSellQueryWrapper.eq("relation_id",smtId);
        swStMarketingTicketGroupSellQueryWrapper.eq("del_flag",0);
        String[] pay_method={"0"}; //现金
        swStMarketingTicketGroupSellQueryWrapper.in("pay_method",pay_method);
        List<Map<String, Object>> groupMaps = swStMarketingTicketGroupSellMapper.selectMaps(swStMarketingTicketGroupSellQueryWrapper);//团购的
        if(groupMaps!=null && groupMaps.size()>0){
            Map<String, Object> res = groupMaps.get(0);
            BigDecimal discount_amount = (BigDecimal)res.get("discount_amount");
            cashRes=cashRes.add(discount_amount);
        }
        QueryWrapper<SwStMarketingTicketOffineSell> swStMarketingTicketOffineSellQueryWrapper=new QueryWrapper<>();
        swStMarketingTicketOffineSellQueryWrapper.select("ifnull(sum(CASH_SELL_AMOUNT),0) as cash_sell_amount");//*100
        swStMarketingTicketOffineSellQueryWrapper.eq("relation_id",smtId);
        swStMarketingTicketOffineSellQueryWrapper.eq("del_flag",0);
        List<Map<String, Object>> map2 = swStMarketingTicketOffineSellMapper.selectMaps(swStMarketingTicketOffineSellQueryWrapper);//现金
        if(map2!=null && map2.size()>0){
            Map<String, Object> res = map2.get(0);
            BigDecimal cash_sell_amount = (BigDecimal)res.get("cash_sell_amount");
            cashRes=cashRes.add(cash_sell_amount);
            if(cashRes==null){
                cashRes = new BigDecimal("0.00");
            }
        }
        return cashRes;
    }



    /**
     * 补提交时--获取乘客事务金额
     乘客事务金额只查现金事务和故障退款金额  以及其他相关数据录入
     故障退款金额一定是负数  sw_st_input_passenger_work_refund
     现金事务有正数和负数    sw_st_input_passenger_work_cash
     其他相关数据录入负数  sw_st_input_passenger_work_other 数据类型为特殊情况退票、正常情况退票 2和3两种情况  20210506增加
     sw_st_input_passenger_work_other 数据类型为发售应急纸票 1， 因大客流发售前往其他运营商应急纸票，以及因大客流发售西安地铁应急纸票  对应产生原因是2和5
     正负数求和后，由实收金额进行减法操作。
     原则是正减负加
     */
    public static List<Map<String, Object>> getPassengerWorkAmountRepair(JSONObject param,SwStConductorSettle settle) throws SQLException, ParseException {
        SwStInputPassengerWorkMapper swStInputPassengerWorkMapper = SpringContextHolder.getBean(SwStInputPassengerWorkMapper.class);
        QueryWrapper<SwStInputPassengerWork> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(SwStInputPassengerWork::getLineCode,settle.getLineCode());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getStationCode,settle.getStationCode());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getWorkDate,settle.getSettleTime());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getDeviceOperateNum,settle.getDeviceOperateNum());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getBomCode,settle.getBomCode());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getClassNumber,settle.getConductorShifts());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getDelFlag,"0");//乘客事务过滤
        List<SwStInputPassengerWork> inputPassengerWork = swStInputPassengerWorkMapper.selectList(queryWrapper);
        Long relation_id = null;
        if(inputPassengerWork!=null && inputPassengerWork.size()>0) {
            SwStInputPassengerWork work = inputPassengerWork.get(0);
            relation_id=work.getId();
        }else {
            return null;
        }
        String lineCode=param.getString("lineCode");
        String stationCode=param.getString("stationCode");
        String conductorJobNo = param.getString("conductorJobNo");
        Date startTime = param.getDate("startTime");
        Date endTime = param.getDate("endTime");
        String startTimeStr = DateFormat.date_ex_str(startTime, "yyyy-MM-dd HH:mm:ss");
        String endTimeStr = DateFormat.date_ex_str(endTime, "yyyy-MM-dd HH:mm:ss");
        String conductorShifts = param.getString("conductorShifts");
        String deviceOperateNum = param.getString("deviceOperateNum");
        String sql="SELECT ifnull(sum(AMOUNT),0) amount from (\n" +//*100
                " select sum(swc.AMOUNT) AMOUNT from sw_st_input_passenger_work_cash swc where swc.DEL_FLAG='0' and swc.RELATION_ID=" +relation_id+
                " union all\n" +
                " select sum(swr.AMOUNT_REFUNDED) AMOUNT from sw_st_input_passenger_work_refund swr where swr.RELATION_ID=" +relation_id+
                " union all \n" +
                " SELECT sum(swo.AMOUNT) AMOUNT from sw_st_input_passenger_work_other swo where swo.DATA_TYPE in('2','3') and swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and DEVICE_OPERATE_NUM='"+deviceOperateNum+"'  and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'"+
                " union all \n" +
                " SELECT sum(swo.AMOUNT) AMOUNT from sw_st_input_passenger_work_other swo where swo.DATA_TYPE='1' and HAPPEN_CAUSE in('2','5') and swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and DEVICE_OPERATE_NUM='"+deviceOperateNum+"'  and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'"+
                ") a\n";
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }

    /**
     * 补提交时，判断有无营销车票
     * @param param
     * @param settle
     * @return
     * @throws SQLException
     * @throws ParseException
     */
	public static Integer isHaveMarketRepair(JSONObject param,SwStConductorSettle settle) throws SQLException, ParseException {
		SwStMarketingTicketMapper swSwStMarketingTicketMapper = SpringContextHolder.getBean(SwStMarketingTicketMapper.class);
        QueryWrapper<SwStMarketingTicket> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(SwStMarketingTicket::getLineCode,settle.getLineCode());
        queryWrapper.lambda().eq(SwStMarketingTicket::getStationCode,settle.getStationCode());
        queryWrapper.lambda().eq(SwStMarketingTicket::getOpertorTime,settle.getSettleTime());
        queryWrapper.lambda().eq(SwStMarketingTicket::getDeviceOperateNum,settle.getDeviceOperateNum());
        queryWrapper.lambda().eq(SwStMarketingTicket::getBomCode,settle.getBomCode());
        queryWrapper.lambda().eq(SwStMarketingTicket::getConductorShifts,settle.getConductorShifts());  
		queryWrapper.lambda().eq(SwStMarketingTicket::getConfirmStatus,"1");  
		queryWrapper.lambda().eq(SwStMarketingTicket::getDelFlag,"0");  //营销车票过滤
        List<SwStMarketingTicket> stMarketingTicket = swSwStMarketingTicketMapper.selectList(queryWrapper);
		if(stMarketingTicket!=null && stMarketingTicket.size()>0) {
           return 1;
        }
		return 0;
	
	}

    /**
     * 补提交时，判断有无乘客无乘客事务
     * @param param
     * @param settle
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    public static Integer isHaveBusRepair(JSONObject param,SwStConductorSettle settle) throws SQLException, ParseException {
        SwStInputPassengerWorkMapper swStInputPassengerWorkMapper = SpringContextHolder.getBean(SwStInputPassengerWorkMapper.class);
        QueryWrapper<SwStInputPassengerWork> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(SwStInputPassengerWork::getLineCode,settle.getLineCode());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getStationCode,settle.getStationCode());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getWorkDate,settle.getSettleTime());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getDeviceOperateNum,settle.getDeviceOperateNum());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getBomCode,settle.getBomCode());
        queryWrapper.lambda().eq(SwStInputPassengerWork::getClassNumber,settle.getConductorShifts());
		queryWrapper.lambda().eq(SwStInputPassengerWork::getConfirmStatus,"1");
        queryWrapper.lambda().eq(SwStInputPassengerWork::getDelFlag,"0");//乘客事务过滤
        List<SwStInputPassengerWork> inputPassengerWork = swStInputPassengerWorkMapper.selectList(queryWrapper);
        String relation_id= null;
        Long sipwId = null;
        if(inputPassengerWork!=null && inputPassengerWork.size()>0) {
            SwStInputPassengerWork work = inputPassengerWork.get(0);
            sipwId=work.getId();
            relation_id=String.valueOf(sipwId);//乘客事务id
        }
        String lineCode=param.getString("lineCode");
        String stationCode=param.getString("stationCode");
        String conductorJobNo = param.getString("conductorJobNo");
        Date startTime = param.getDate("startTime");
        Date endTime = param.getDate("endTime");
        String startTimeStr = DateFormat.date_ex_str(startTime, "yyyy-MM-dd HH:mm:ss");
        String endTimeStr = DateFormat.date_ex_str(endTime, "yyyy-MM-dd HH:mm:ss");
        String conductorShifts = param.getString("conductorShifts");
        String deviceOperateNum = param.getString("deviceOperateNum");

        String sql1="SELECT * from sw_st_input_passenger_work_ticket swt where swt.LINE_CODE='"+lineCode+"' and swt.STATION_CODE='"+stationCode+"' and swt.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swt.HANDLE_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and swt.DEVICE_OPERATE_NUM='"+deviceOperateNum+"' and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'";

        String sql2=" SELECT * from sw_st_input_passenger_work_other swo where swo.LINE_CODE='"+lineCode+"' and swo.STATION_CODE='"+stationCode+"' and swo.CONDUCTOR_CODE='"+conductorJobNo+"' and \n" +
                " swo.INPUT_TIME BETWEEN '"+startTimeStr+"' and '"+endTimeStr+"' and swo.DEVICE_OPERATE_NUM='"+deviceOperateNum+"'  and CLASS_NUMBER='" +conductorShifts+"' and DEL_FLAG='0'";
        List<Map<String, Object>> maps1 = JdbcUtil.exeSqlQuery(sql1);
        List<Map<String, Object>> maps2 = JdbcUtil.exeSqlQuery(sql2);
        if((StringUtils.isEmpty(relation_id) || "null".equals(relation_id) || "NULL".equals(relation_id)) && maps1.size()==0 && maps2.size()==0){
            return 0; //空，或者数量为0就无乘客无乘客事务
        }
        return 1;
    }


    /**
     * 补提交获取sc,ls,acc
     * @param param
     * @return
     * @throws SQLException
     */
    public static Map<String,BigDecimal> getScLcAccRepair(Map<String,Object> param) throws SQLException, ParseException {
        Object lineCode=param.get("lineCode");
        Object stationCode=param.get("stationCode");
        String type = "2";
        Object bomCode= param.get("bomCode");
        String deviceOperateNum= (String)param.get("deviceOperateNum");
        if(StringUtils.isEmpty(deviceOperateNum)){
            deviceOperateNum= (String)param.get("equipmentNumber");
        }
        bomCode=bomCode.toString().replace(",","','");
        deviceOperateNum=deviceOperateNum.replace(",","','");
        Object startTime = DateFormat.ex_strDate_to_date(String.valueOf(param.get("startTime")),"yyyyMMdd");
        Object endTime = DateFormat.ex_strDate_to_date(String.valueOf(param.get("endTime")),"yyyyMMdd");
        List<Map<String, Object>> scMap = JdbcUtil.exeSqlQuery("select \n" +
                        "ifnull(sum(sale_txnamtall),0) as sc_sell,  \n" +
                        "ifnull(sum(recharge_txnamtall),0) as sc_de ,\n" +
                        "ifnull(sum(refund_txnamtall),0) as sc_return, \n" +
                        "ifnull(sum(supplement_txnamtall),0) as sc_su \n" +
                        "from sc_fare_statistics \n" +
                        "where equipmenttype='"+type+"'\n" +
                        "and station_equipment_number in ('"+bomCode+"')\n" +
                        "and posoperid in ('"+deviceOperateNum+"') "+
                        " and (paymethod='0' or paymethod='1' or paymethod='10' or paymethod='255' OR paymethod is NULL or paymethod like '%3130%')\n" +  //增加paymethod is null的情况
                        "and lineid='"+lineCode+"'\n" +
                        "and station_number='"+stationCode+"'\n" +
                        "and bookdate ='"+startTime+"'"
                , "sc_sell,sc_de,sc_return");

        List<Map<String, Object>> lcMap = JdbcUtil.exeSqlQuery("select \n" +
                        "ifnull(sum(sale_txnamtall),0) as lc_sell,  \n" +
                        "ifnull(sum(recharge_txnamtall),0) as lc_de ,\n" +
                        "ifnull(sum(refund_txnamtall),0) as lc_return, \n" +
                        "ifnull(sum(supplement_txnamtall),0) as lc_su \n" +
                        "from lc_fare_statistics \n" +
                        "where equipmenttype='"+type+"'\n" +
                        "and station_equipment_number in ('"+bomCode+"')\n" +
                        "and posoperid in ('"+deviceOperateNum+"') "+
                        " and (paymethod='0' or paymethod='1' or paymethod='10' or paymethod='255' OR paymethod is NULL or paymethod like '%3130%')\n" +  //增加paymethod is null的情况
                        "and lineid='"+lineCode+"'\n" +
                        "and station_number='"+stationCode+"'\n" +
                        "and bookdate ='"+startTime+"'"
                , "lc_sell,lc_de,lc_return");

        List<Map<String, Object>> accMap = JdbcUtil.exeSqlQuery("select \n" +
                        "ifnull(sum(sale_txnamtall),0) as acc_sell,  \n" +
                        "ifnull(sum(recharge_txnamtall),0) as acc_de ,\n" +
                        "ifnull(sum(refund_txnamtall),0) as acc_return ,\n" +
                        "ifnull(sum(supplement_txnamtall),0) as acc_su \n" +
                        "from acc_fare_statistics \n" +
                        "where equipmenttype in ('2','7')\n" +   //20221230  6号线BOM类型传的是7，智慧票亭
                        "and station_equipment_number in ('"+bomCode+"')\n" +
                        "and posoperid in ('"+deviceOperateNum+"') "+
                        " and (paymethod='0' or paymethod='1' or paymethod='10' or paymethod='255' OR paymethod is NULL or paymethod like '%3130%')\n" +   //增加paymethod is null的情况
                    	"and lineid='"+lineCode+"'\n" +
                        "and station_number='"+stationCode+"'\n" +
//                "and bookdate='"+revenueTime+"'"
                        "and bookdate ='"+startTime+"'"
                , "acc_sell,acc_de,acc_return");

        Map<String,BigDecimal> res=new HashMap<>();
        if(scMap.size()>0){
            res.put("sc_sell",(BigDecimal)scMap.get(0).get("sc_sell"));
            res.put("sc_de",(BigDecimal)scMap.get(0).get("sc_de"));
            res.put("sc_return",(BigDecimal)scMap.get(0).get("sc_return"));
            res.put("sc_su",(BigDecimal)scMap.get(0).get("sc_su"));
        }else{
            res.put("sc_sell",new BigDecimal(0));
            res.put("sc_de",new BigDecimal(0));
            res.put("sc_return",new BigDecimal(0));
            res.put("sc_su",(BigDecimal)scMap.get(0).get("sc_su"));
        }

        if(lcMap.size()>0){
            res.put("lc_sell",(BigDecimal)lcMap.get(0).get("lc_sell"));
            res.put("lc_de",(BigDecimal)lcMap.get(0).get("lc_de"));
            res.put("lc_return",(BigDecimal)lcMap.get(0).get("lc_return"));
            res.put("lc_su",(BigDecimal)lcMap.get(0).get("lc_su"));
        }else{
            res.put("lc_sell",new BigDecimal(0));
            res.put("lc_de",new BigDecimal(0));
            res.put("lc_return",new BigDecimal(0));
            res.put("lc_su",(BigDecimal)lcMap.get(0).get("lc_su"));
        }

        if(accMap.size()>0){
            res.put("acc_sell",(BigDecimal)accMap.get(0).get("acc_sell"));
            res.put("acc_de",(BigDecimal)accMap.get(0).get("acc_de"));
            res.put("acc_return",(BigDecimal)accMap.get(0).get("acc_return"));
            res.put("acc_su",(BigDecimal)accMap.get(0).get("acc_su"));
        }else{
            res.put("acc_sell",new BigDecimal(0));
            res.put("acc_de",new BigDecimal(0));
            res.put("acc_return",new BigDecimal(0));
            res.put("acc_su",(BigDecimal)accMap.get(0).get("acc_su"));
        }
        return res;
    }
}
